<?php
/**
 * mssql数据库架构处理类
 *
 * @author Mo yi <root@imoi.cn>
 * @link   http://www.imoi.cn
 * @copyright Copyright &copy; 2010-2012 DuoLam Software LLC
 * @license http://www.imoi.cn/license/
 * @version $Id: dpMssqlSchema.php 2799 2012-04-10 09:31:13Z Mo.yi $
 * @package db.schema
 * @since 1.0
 */

class dpMssqlSchema extends dpSchema implements ISchema
{
    /**
     * 数据表主键
     */
    protected $_pk;

    /** 
     * 字段类型
     */
    public $columnType = array
    (
		'pk'	   => 'int IDENTITY PRIMARY KEY',
        'string'   => 'varchar(255)',
        'text'	   => 'text',
        'integer'  => 'int',
        'int_not'  => 'int NOT NULL DEFAULT ((0))',
        'float'    => 'float',
        'decimal'  => 'decimal',
        'datetime' => 'datetime',
        'date'	   => 'date',
        'binary'   => 'binary',
        'boolean'  => 'bit',
        'bool_not' => 'bit NOT NULL DEFAULT ((0))',
    );

    /**
     * 获取表字段信息
     * @param string $table 表名
     * @return array  字段信息列表
     */
    public function getColumn($table)
    {
        $caching = (int)D::$config['component']['db']['schema_caching'];
		$sql = "SELECT column_name AS name, data_type AS type, column_default AS def, is_nullable AS is_null
        FROM information_schema.tables AS t
        JOIN information_schema.columns AS c
        ON  t.table_catalog = c.table_catalog
        AND t.table_schema = c.table_schema
        AND t.table_name = c.table_name
        WHERE   t.table_name = '$table'";

		if (($columns=$this->connect->finds($sql, $caching))===array()) return false;

        $pk = $this->findPrimaryKey($table);

		foreach ($columns as $k => $column) {
			$columns[$k]['name']    = $column['name'];
            $columns[$k]['default'] = $column['def'];
            $columns[$k]['type']    = $column['type'];

            if ($column['name'] == $pk) {
                $columns[$k]['pk']  = true;
                $columns[$k]['auto']= true;
                $this->_pk = $pk;
            }
		}
        return $columns;
    }

    /**
     * 获取SQL server的表主键
     * @param string $table 表名
     * @return string 表主键字段名
     */
    protected function findPrimaryKey($table)
    {
        $sql = "SELECT A.COLUMN_NAME AS name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE" 
            . " A join(SELECT * FROM sysobjects WHERE xtype=N'PK' ) B ON "
            . "object_id(A.CONSTRAINT_NAME)=B.id WHERE A.table_name='$table'";
        $primary = $this->connect->finds($sql);

        switch (count($primary)) {
            case 0: 
                $primary = false;
                break;
            case 1:
                $primary = $primary[0]['name'];
                break;
        }

        return $primary;
    }

    /**
     * 重载父类方法，对表名或字段加上SQL server特有符号
     * @param string $name
     * @return string
     */
    protected function quoteSimpleName($name)
    {
        return '[' . $name . ']';
    }

    /**
     * 获取SQL server LIMIT部分语句
     * @param int $limit  起始行数位置
     * @param int $offset 限制行数
     * @param string $sql SQL语句
     * @return string LIMIT部分的SQL语句
     */
    public function getLimitString($limit, $offset = '', &$sql)
    {
        if (!is_int($limit) && empty($limit)) return null;

        preg_match('/(select\sdistinct|select)/i', $sql['select'], $matche);

        if (empty($matche)) return null;
        else {
            $select = $matche[0];
            $s      = substr($sql['select'], strlen($select)+1);
        }

        if (empty($offset) && strpos($limit, ',') === false) {
            $sql['select'] = $select . ' TOP ' . $limit . ' ' . $s;
            return null;
        } else {

            if (strpos($limit, ',') !== false) {
                $limit  = explode(',', $limit);
                $offset = $limit[1];
                $limit  = $limit[0];
            }

            if (isset($sql['where'])) {
                preg_match('/where/i', $sql['where'], $matcheW);
                $where = $matcheW[0];
                $w     = substr($sql['where'], strlen($where)+1);
            } else
                $where = ' WHERE ';

            $sql['select'] = $select . ' TOP ' . $offset . ' ' . $s;
            preg_match('/\{\w+\}/i',$sql['from'],$table);
            $columns = $this->cmd->getColumn($table[0]);

            foreach ($columns as $c) {
                if ($c['pk']) {
                    $pk = $c['name'];
                    break;
                }
            }

            $sql['where'] = $where . '(' . $pk . ' NOT IN (SELECT TOP ' . 
                            $limit*$offset . ' ' . $pk . ' FROM [' . $table[0] .']';
            $sql['where'].= isset($w) ? ' WHERE ' . $w . ' ' : ' ';
            $sql['where'].= isset($sql['order']) ? $sql['order'] . '))' : '))';

            if (isset($w)) $sql['where'] .= ' AND ' . $w;

            return null;
        }
    }

    /**
     * 获取表主键名称
     * @return string 主键名字
     */
    public function getPk()
    {
        return !empty($this->_pk) ? $this->_pk : false;
    }

    /**
     * 获取重命名表的SQL语句
     * @param string $name    表名称
     * @param string $newName 表新名称
     * @return string 返回重命名表的SQL语句
     */
    public function getRenameTableString($name, $newName)
    {
        return "sp_rename '$name', '$newName'";
    }

    /**
     * 获取清空数据表的SQL语句
     * @param string $table 表名称
     * @return string 返回清空数据表的SQL语句
     */
    public function getTruncateTable($table)
    {
        return 'TRUNCATE TABLE '.$this->quoteSimpleName($table);
    }

    /**
     * 获取更改字段名的SQL语句
     * @param string $table   表名称
     * @param string $name    字段名
     * @param string $newName 新字段名
     * @return string 返回更改字段名的SQL语句
     */
    public function getRenameColumnString($table, $name, $newName)
    {
        return "sp_rename '$table.$name', '$newName', 'COLUMN'";
    }

    /**
     * 获取修改字段的SQL语句
     * @param string $tableName 表名称
     * @param string $name      字段名称
     * @param string $type      字段类型
     * @return string 返回修改字段的SQL语句
     */
    public function getAlterColumnString($table, $name, $type)
    {
        return 'ALTER TABLE ' . $this->quoteSimpleName($table) . ' ALTER COLUMN '
            . $this->quoteSimpleName($name) . ' '
            . $this->getColumnType($type);
    }

    /**
     * 获取删除数据表的SQL语句
     * @param string $tableName 表名称
     * @return string 返回删除数据表的SQL语句
     */
    public function getDropTableString($tableName)
    {
        $string  = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ";
        $string .= "'{$tableName}') DROP TABLE {$tableName}";
        return $string;
    }

}